package com.glsc.ngateway.common.base.repo.mysql.gateway.platform.it.networkAssets;

import com.glsc.ngateway.common.base.domain.mysql.gateway.platform.it.networkassets.HostInformationMaintenance;
import com.glsc.ngateway.common.api.platform.dto.it.networkassets.HostInformationMaintenanceDto;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Repository;

import javax.validation.constraints.NotNull;
import java.util.List;

/**
 * @author xuchang
 */
@Repository
public interface HostInformationMaintenanceRepo extends JpaRepository<HostInformationMaintenance, Long> {

    @Query(value = "select * from t_gw_it_net_assets_host_information h " +
            "where deleted = 0 " +
            "and if ( :#{#hostInfoMtDto.host} is not null, h.host= :#{#hostInfoMtDto.host}, 1=1) " +
            "and if ( :#{#hostInfoMtDto.hostname} is not null, h.hostname like CONCAT('%', :#{#hostInfoMtDto.hostname},'%'), 1=1) " +
            "and if ( :#{#hostInfoMtDto.serviceZone} is not null, h.service_zone like CONCAT('%', :#{#hostInfoMtDto.serviceZone},'%'), 1=1) " +
            "and if ( :#{#hostInfoMtDto.deviceRole} is not null, h.device_role= :#{#hostInfoMtDto.deviceRole}, 1=1) " +
            "and if ( :#{#hostInfoMtDto.snmpVersion} is not null, h.snmp_version= :#{#hostInfoMtDto.snmpVersion}, 1=1) " +
            "and if ( :#{#hostInfoMtDto.snmpRoCommunity} is not null, h.snmp_ro_community= :#{#hostInfoMtDto.snmpRoCommunity}, 1=1) " +
            "and if ( :#{#hostInfoMtDto.mgmtInterface} is not null, h.mgmt_interface= :#{#hostInfoMtDto.mgmtInterface}, 1=1) " +
            "and if ( :#{#hostInfoMtDto.username} is not null, h.username= :#{#hostInfoMtDto.username}, 1=1) " +
            "and if ( :#{#hostInfoMtDto.hostid} is not null, h.hostid like CONCAT('%', :#{#hostInfoMtDto.hostid},'%'), 1=1) " +
            "and if ( :#{#hostInfoMtDto.taskList} is not null, JSON_CONTAINS(h.task, JSON_ARRAY( :#{#hostInfoMtDto.taskList} )), 1=1) " +
            "and if ( :#{#hostInfoMtDto.bindSerialNumList} is not null, JSON_CONTAINS(h.bind_serial_num, JSON_ARRAY( :#{#hostInfoMtDto.bindSerialNumList} )), 1=1) " +
            "and if ( :#{#hostInfoMtDto.deployedModeList} is not null, JSON_CONTAINS(h.deployed_mode, JSON_ARRAY( :#{#hostInfoMtDto.deployedModeList} )), 1=1) " +
            "and if ( :#{#hostInfoMtDto.peer} is not null, h.peer like CONCAT('%', :#{#hostInfoMtDto.peer},'%'), 1=1) " +
            "", nativeQuery = true)
    Page<HostInformationMaintenance> page(Pageable pageable, @Param("hostInfoMtDto") HostInformationMaintenanceDto hostInfoMtDto);

    @Query(value = "select * from t_gw_it_net_assets_host_information h " +
            "where deleted = 0 " +
            "and if ( :#{#hostInfoMtDto.host} is not null, h.host= :#{#hostInfoMtDto.host}, 1=1) " +
            "and if ( :#{#hostInfoMtDto.hostname} is not null, h.hostname like CONCAT('%', :#{#hostInfoMtDto.hostname},'%'), 1=1) " +
            "and if ( :#{#hostInfoMtDto.serviceZone} is not null, h.service_zone like CONCAT('%', :#{#hostInfoMtDto.serviceZone},'%'), 1=1) " +
            "and if ( :#{#hostInfoMtDto.deviceRole} is not null, h.device_role= :#{#hostInfoMtDto.deviceRole}, 1=1) " +
            "and if ( :#{#hostInfoMtDto.snmpVersion} is not null, h.snmp_version= :#{#hostInfoMtDto.snmpVersion}, 1=1) " +
            "and if ( :#{#hostInfoMtDto.snmpRoCommunity} is not null, h.snmp_ro_community= :#{#hostInfoMtDto.snmpRoCommunity}, 1=1) " +
            "and if ( :#{#hostInfoMtDto.mgmtInterface} is not null, h.mgmt_interface= :#{#hostInfoMtDto.mgmtInterface}, 1=1) " +
            "and if ( :#{#hostInfoMtDto.username} is not null, h.username= :#{#hostInfoMtDto.username}, 1=1) " +
            "and if ( :#{#hostInfoMtDto.hostid} is not null, h.hostid like CONCAT('%', :#{#hostInfoMtDto.hostid},'%'), 1=1) " +
            "and if ( :#{#hostInfoMtDto.taskList} is not null, JSON_CONTAINS(h.task, JSON_ARRAY( :#{#hostInfoMtDto.taskList} )), 1=1) " +
            "and if ( :#{#hostInfoMtDto.bindSerialNumList} is not null, JSON_CONTAINS(h.bind_serial_num, JSON_ARRAY( :#{#hostInfoMtDto.bindSerialNumList} )), 1=1) " +
            "and if ( :#{#hostInfoMtDto.deployedModeList} is not null, JSON_CONTAINS(h.deployed_mode, JSON_ARRAY( :#{#hostInfoMtDto.deployedModeList} )), 1=1) " +
            "and if ( :#{#hostInfoMtDto.peer} is not null, h.peer like CONCAT('%', :#{#hostInfoMtDto.peer},'%'), 1=1) " +
            "", nativeQuery = true)
    List<HostInformationMaintenance> queryAll(@Param("hostInfoMtDto") HostInformationMaintenanceDto hostInfoMtDto);

    void deleteById(Long id);

    @Query(value = "select count(*) from t_gw_it_net_assets_host_information h " +
            "where deleted = 0 " +
            "and if ( :id is not null, h.id != :id, 1=1) " +
            "and h.host = :host " +
            "", nativeQuery = true)
    Integer countByHost(@Nullable @Param("id") Long id, @NotNull @Param("host") String host);

    List<HostInformationMaintenance> findAllByHostIn(List<String> readHostList);
}
